﻿Imports System.Data
Imports System.Data.SqlClient

Public Class frm_Duration

    Private Sub btn_cancel_Click(sender As Object, e As EventArgs) Handles btn_cancel.Click
        Me.Close()
    End Sub

    Private Sub btn_update_Click(sender As Object, e As EventArgs) Handles btn_update.Click

        Dim i, j As Integer
        Dim t As Integer
        Dim failValidation As Boolean = False

        With data1
            'initilize
            For i = 0 To .RowCount - 2
                For j = 0 To .ColumnCount - 1
                    .Rows(i).Cells(j).Style.BackColor = Color.White
                Next
            Next

            'validate empty cell
            For i = 0 To .RowCount - 2
                For j = 0 To .ColumnCount - 1
                    If (.Rows(i).Cells(j).Value Is Nothing) OrElse _
                        (.Rows(i).Cells(j).Value.ToString.Trim = "") Then
                        .Rows(i).Cells(j).Style.BackColor = Color.Red
                        failValidation = True
                    End If

                Next
            Next
            If failValidation Then
                MessageBox.Show("Please fill in empty cell(s).")
                Exit Sub
            End If

            'validate all the Models: no repeat
            For i = 0 To .RowCount - 3
                For j = i + 1 To .RowCount - 2
                    If .Rows(i).Cells(0).Value = .Rows(j).Cells(0).Value Then
                        .Rows(i).Cells(0).Style.BackColor = Color.Red
                        .Rows(j).Cells(0).Style.BackColor = Color.Red
                        failValidation = True
                    End If
                Next
            Next
            If failValidation Then
                MessageBox.Show("There are more than 1 row for the same model.")
                Exit Sub
            End If

            'validate duration in integer
            For i = 0 To .RowCount - 2
                For j = 1 To .ColumnCount - 1
                    Try
                        t = Integer.Parse(.Rows(i).Cells(j).Value)
                    Catch ex As Exception
                        .Rows(i).Cells(j).Style.BackColor = Color.Red
                        failValidation = True
                    End Try
                Next
            Next
            If failValidation Then
                MessageBox.Show("The duration must be an integer.")
                Exit Sub
            End If

            'all good now, update DB
            'first delete all old data, then insert new
            Using connection As New SqlConnection(ConnectionString)
                connection.Open()
                Dim sql = "DELETE FROM Cycle"

                Dim cmd As New SqlCommand(sql, connection)
                cmd.CommandType = CommandType.Text
                cmd.ExecuteNonQuery()
                cmd.Dispose()

                For i = 0 To .RowCount - 2
                    For j = 1 To .ColumnCount - 1
                        sql = String.Format("INSERT INTO Cycle(id, ModelId, StepId, Duration) VALUES({0}, '{1}','{2}',{3})", _
                                            i, _
                                            .Rows(i).Cells(0).Value, _
                                            .Columns(j).HeaderText, _
                                            .Rows(i).Cells(j).Value)
                        cmd = New SqlCommand(sql, connection)
                        cmd.ExecuteNonQuery()
                        cmd.Dispose()
                    Next
                Next
            End Using
            MessageBox.Show("Update successfully")
            Me.Close()
        End With
    End Sub

    Private Sub frm_Duration_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Using connection As New SqlConnection(ConnectionString)
            connection.Open()
            Dim sql = "SELECT * FROM Cycle ORDER BY id, StepId"

            Dim cmd As New SqlCommand(sql, connection)
            cmd.CommandType = CommandType.Text

            Dim reader As SqlDataReader = cmd.ExecuteReader
            Dim model, stepId As String
            Dim t, r As Integer

            data1.Rows.Clear()
            r = -1
            While reader.Read()
                model = reader.GetString(0)
                stepId = reader.GetString(1)
                t = reader.GetInt32(2)

                If (stepId = "BDY") Then
                    data1.Rows.Add()
                    r += 1
                End If

                data1.Rows(r).Cells(0).Value = model
                Select Case stepId
                    Case "BDY" : data1.Rows(r).Cells("col_BDY").Value = t
                    Case "PNT" : data1.Rows(r).Cells("col_PNT").Value = t
                    Case "TRI" : data1.Rows(r).Cells("col_TRI").Value = t
                    Case "CHA" : data1.Rows(r).Cells("col_CHA").Value = t
                    Case "FNG" : data1.Rows(r).Cells("col_FNG").Value = t
                End Select
            End While

            reader.Close()
            cmd.Dispose()
        End Using
    End Sub
End Class